﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel;

namespace temp_load_database
{
    public partial class frm : Form
    {
        DataBase m_db = null;
        public frm()
        {
            InitializeComponent();
            Connect_To_DataBase();
        }
        private void Connect_To_DataBase()
        {
            DataBase db = null;
            string computer = System.Environment.MachineName;
            //if (DataBase.ConnectToDataBase("sqlserver", "",
            //         computer + "\\SQLEXPRESS", "", "", "cross_section", true, ref db))
            //{
            //    m_db = db;
            //}
            if (!DataBase.ConnectToDataBase("sqlserver", "192.168.5.32", "1433", "",
                     computer + "\\SQLEXPRESS", "sa", "123", "cross_section", false, ref db))
            {

                //if (MessageBox.Show("无法连接数据库\"cross_section\" ,是否要在本地创建新的数据库?", "Information", MessageBoxButtons.YesNo) == DialogResult.Yes)
                //{
                //    //string myExecuteQuery;
                //    //myExecuteQuery = "CREATE DATABASE cross_section ON PRIMARY " +
                //    //"(NAME = cross_section, " +
                //    //"FILENAME = 'C:\\cross_section.mdf', " +
                //    //"SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
                //    //"LOG ON (NAME = cross_section_log, " +
                //    //"FILENAME = 'C:\\cross_section.ldf', " +
                //    //"SIZE = 5MB, " +
                //    //"MAXSIZE = 200MB, " +
                //    //"FILEGROWTH = 10%)";

                //    //SqlConnection mySqlConnection = new SqlConnection("server=" + computer + "\\SQLEXPRESS;integrated security = true");
                //    //SqlCommand myCommand = new SqlCommand(myExecuteQuery, mySqlConnection);
                //    //myCommand.Connection.Open();
                //    //myCommand.ExecuteNonQuery();
                //    //mySqlConnection.Close();
                //    //bool c = DataBase.ConnectToDataBase("sqlserver", "",
                //    //    computer + "\\SQLEXPRESS", "", "", "cross_section", true, ref db);
                //    //m_db = db;

                //}
                //else
                //{

                //    return;
                //}
            }
            else
            {
                m_db = db;
            }
        }

        private void btn_open_Click(object sender, EventArgs e)
        {
            OpenFileDialog op = new OpenFileDialog();
            if (op.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtpath.Text = op.FileName;
            }
        }



        #region 第一页
        private void btnsave_Click(object sender, EventArgs e)
        {
            ApplicationClass excel = new ApplicationClass();
            object miss = Type.Missing;
            Workbook book = excel.Workbooks.Open(txtpath.Text, miss, miss, miss, miss, miss, 
                miss, miss, miss, miss, miss, miss, miss, miss, miss);
            excel.Visible = true;
            Worksheet sheet1 = book.Sheets[1] as Worksheet;
            Worksheet sheet2 = book.Sheets[2] as Worksheet;
            Range r1 = sheet1.Cells[7, 11] as Range;
            Range r2 = sheet2.Cells[7, 11] as Range;
            for (int k = 0; k < 100; k++)
            {
                for (int i = 0; i < 8; i++)
                {

                    if (r1.Value2!=null && r1.Value2.ToString() != "")
                    {
                        double[] vals = new double[7];//特征点的变形
                        for (int j = 0; j < 56; j++)
                        {
                            try
                            {
                                r2.get_Offset(j, 0).Value2 = (double)r2.get_Offset(j, 0).Value2 -
                                    (double)r1.get_Offset(j, 0).Value2;
                                if ((j-1) % 9 == 0)
                                {
                                    vals[(j - 1) / 9] = ((double)r2.get_Offset(j, 0).Value2 
                                        + (double)r2.get_Offset(j - 1, 0).Value2) / 2;
                                }

                            }
                            catch
                            {
                                continue;
                            }
                        }
                        if (i <= 3)
                        {
                            int mod = i % 4;
                            Range tb = sheet2.Cells[r2.Row + mod * 15, 2] as Range;
                            Range tb1 = sheet1.Cells[r2.Row + mod * 15, 2] as Range;
                            tb.get_Offset(-2, 0).Value = tb1.get_Offset(-2, 0).Value;
                            tb.get_Offset(-3, 1).Value = tb1.get_Offset(-3, 1).Value;
                            for (int m = 0; m < vals.Length; m++)
                            {
                                tb.get_Offset(0, m).Value2 = vals[m];
                            }

                        }
                        else
                        {
                            int mod = i % 4;
                            Range tb = sheet2.Cells[r2.Row + mod * 15, 30] as Range;
                            Range tb1 = sheet1.Cells[r2.Row + mod * 15, 30] as Range;
                            tb.get_Offset(-2, 0).Value = tb1.get_Offset(-2, 0).Value;
                            tb.get_Offset(-3, 1).Value = tb1.get_Offset(-3, 1).Value;
                            for (int m = 0; m < vals.Length; m++)
                            {
                                tb.get_Offset(0, m).Value2 = vals[m];
                            }
                        }
                    }
                    if (i == 3)
                    {
                        r1 = r1.get_Offset(0, 3);
                        r2 = r2.get_Offset(0, 3);
                    }
                    else
                    {
                        r1 = r1.get_Offset(0, 2);
                        r2 = r2.get_Offset(0, 2);
                    }
                }
                r1 = sheet1.Cells[7 + 62 * (k + 1), 11] as Range;
                r2 = sheet2.Cells[7 + 62 * (k + 1), 11] as Range;
            }
            MessageBox.Show("搞定了!");

        }
        #endregion

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog op = new OpenFileDialog();
            if (op.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                textBox1.Text = op.FileName;
            }
            ApplicationClass excel = new ApplicationClass();
            object miss = Type.Missing;
            Workbook book = excel.Workbooks.Open(textBox1.Text, miss, miss, miss, miss, miss,
                miss, miss, miss, miss, miss, miss, miss, miss, miss);
            combo.Items.Clear();
            combo2.Items.Clear();
            for (int i = 1; i <= book.Sheets.Count; i++)
            {
                combo.Items.Add((book.Sheets[i] as Worksheet).Name);
                combo2.Items.Add((book.Sheets[i] as Worksheet).Name);
            }
            book.Close(false, miss, miss);
            excel.Quit();
        }

        private void btn_static_Click(object sender, EventArgs e)
        {
            ApplicationClass excel = new ApplicationClass();
            object miss = Type.Missing;
            Workbook book = excel.Workbooks.Open(textBox1.Text, miss, miss, miss, miss, miss,
                miss, miss, miss, miss, miss, miss, miss, miss, miss);
            Worksheet sheet = book.Sheets[combo.SelectedItem] as Worksheet;
            Worksheet sheetout = book.Sheets[combo2.SelectedItem] as Worksheet;
            Range r3 = sheetout.Cells[2, 1] as Range;
            excel.Visible = true;
            r3.get_Offset(-1,0).Value2="断面号";
             r3.get_Offset(-1,1).Value2="水平直径";
             r3.get_Offset(-1,2).Value2="垂直直径";
             r3.get_Offset(-1,3).Value2="最大直径";
             r3.get_Offset(-1,4).Value2="最大直径角度";
             r3.get_Offset(-1,5).Value2="最小直径";
             r3.get_Offset(-1,6).Value2="最小直径角度";
             r3.get_Offset(-1,7).Value2="里程";
            Range r2 = sheet.Cells[7, 11] as Range;
            for (int k = 0; k < 100; k++)
            {
                for (int i = 0; i < 8; i++)
                {

                    if (r2.Value2 != null && r2.Value2.ToString() != "")
                    {
                        double hor = 0, verti = 0, max = 0, maxangle = 0, min = 0, minangle = 0;
                        get_val(r2,  ref hor, ref verti, ref max, ref maxangle, ref min, ref minangle);
                        r3.Value2 = r2.get_Offset(-3, 0).Value2;
                        r3.get_Offset(0, 1).Value2 = hor;
                        r3.get_Offset(0, 2).Value2 = verti;
                        r3.get_Offset(0, 3).Value2 = max;
                        r3.get_Offset(0, 4).Value2 = maxangle;
                        r3.get_Offset(0, 5).Value2 = min;
                        r3.get_Offset(0,6).Value2 = minangle;
                        Range tb;
                        int mod = i % 4;
                        if (i <= 3)
                        {
                     
                            tb = sheet.Cells[r2.Row + mod * 15, 2] as Range;
                        }
                        else
                        {
                            tb = sheet.Cells[r2.Row + mod * 15, 30] as Range;
                           
                        }
                        r3.get_Offset(0, 7).Value2 = tb.get_Offset(-2, 0).Text;

                        r3 = r3.get_Offset(1, 0);
                    }
                    if (i == 3)
                    {
                      
                        r2 = r2.get_Offset(0, 3);
                    }
                    else
                    {
                        r2 = r2.get_Offset(0, 2);
                    }
                }
                r2 = sheet.Cells[7 + 62 * (k + 1), 11] as Range;
            }
        }

        void get_val(Range r, ref double hor, ref double verti, ref double max, ref double maxangle,
            ref double min, ref double minangle)
        {
            hor = ((double)r.get_Offset(9, 1).Value2 + (double)r.get_Offset(10, 1).Value2
                + (double)r.get_Offset(45, 1).Value2 + (double)r.get_Offset(46, 1).Value2) / 2000 
                + 5.5;
            verti = ((double)r.get_Offset(9, 1).Value2 + (double)r.get_Offset(10, 1).Value2) / 2000
                + 5.5;
              double max_zj = 0;
            double max_jd = 42.5;
            double min_zj = 999;
            double min_jd = 42.5;
            for (int i = 0; i < 36; i++)
            {
                double zj;
                if (i <= 19)
                {
                    zj = Convert.ToDouble(r.get_Offset(i, 1).Value2) / 1000 + Convert.ToDouble(r.get_Offset(i+36, 1).Value2) / 1000 
                        + 5.5;
                }
                else
                {
                    zj = Convert.ToDouble(r.get_Offset(i, 1).Value2) / 1000 + 5.5;
                }
                if (zj > max_zj)
                {
                    max_zj = zj;
                    max_jd = 42.5 + 5 * i;
                }
                if (zj < min_zj)
                {
                    min_zj = zj;
                    min_jd = 42.5 + +5 * i;
                }
            }
            max = max_zj;
            maxangle = max_jd;
            min = min_zj;
            minangle = min_jd;
        }

    }
}
